﻿using System.IO;
using System.Net;
//using Excel = Microsoft.Office.Interop.Excel;
using Excel2 = Microsoft.Office.Interop.Excel;
//using Microsoft.Office.Interop.Excel;
using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace LGPSDataGen.Generators
{
    /// <summary>
    /// PJM Generator has to gather load generation data from PJM website:
    /// http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/YYYY-hourly-loads.ashx
    /// Then extract the zip file (named "YYYY hourly_loads.xls")
    /// To read that data: Cell [2,3] = 1/1/20XX 00:00
    /// Cell [2,26] = 1/1/20XX 23:00
    /// Cell [366,26] = 12/31/20XX 23:00
    /// </summary>
    public class PJMGenerator : Generator, IGenerator
    {
        private string pathToFile;
        private string content = "";
        private const string PECO = "PE";
        
        //All of these are static links for the different years, but luckily the data formate is the same for each
        public PJMGenerator(DateTime theDate)
        {
            mDate = theDate;
            if (mDate.Year >= 2008 || mDate.Year == 2005) 
            {
                content = "http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/" + mDate.Year.ToString() + "-hourly-loads.ashx";
            }
            else if (mDate.Year == 2007)
            {
                content = "http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/" + mDate.Year.ToString() + "-hourly_loads.ashx";
            }
            else if (mDate.Year == 2006) //XLS
            {
                content = "http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/2006-hourly_loads.ashx";
            }
            else if (mDate.Year == 2004)
            {
                content = "http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/hourly-loads-2004.ashx";
            }
            else if (mDate.Year == 2003) //XLS
            {
                content = "http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/hourly_loads_2003.ashx";
            }
            else if (mDate.Year == 2002) //XLS
            {
                content = "http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/hourly_loads_2002.ashx";
            }
            else if (mDate.Year == 2001) //XLS
            {
                content = "http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/hourly_loads_2001.ashx";
            }
            else if (mDate.Year == 2000) //XLS
            {
                content = "http://www.pjm.com/markets-and-operations/compliance/nerc-standards/~/media/markets-ops/compliance/historical-load-data/hourly_loads_2001.ashx";
            }

            string timeStamp = mDate.Year + "_" + mDate.Month + "_" + mDate.Day;
            if (mDate.Year >= 2008 || mDate.Year == 2007 || mDate.Year == 2005 || mDate.Year == 2004)
            {
                pathToFile = @"PJM" + mDate.Year + ".zip";
            }
            else
            {
                pathToFile = mDate.Year + " hourly_loads.xls";
            }
        }

        public override void DownloadData()
        {
            if(!File.Exists(pathToFile))
            {
                WebClient wc = new WebClient();
                wc.DownloadFile(content, pathToFile);
            }
        }

        public override void ExtractData()
        {
            //We only need to extract certaion years
            if (mDate.Year >= 2008 || mDate.Year == 2007 || mDate.Year == 2005 || mDate.Year == 2004)
            {
                StreamReader sr = new StreamReader(pathToFile);

                Ionic.Zip.ZipFile z = new Ionic.Zip.ZipFile(pathToFile);
                z.ExtractAll(Directory.GetCurrentDirectory(), Ionic.Zip.ExtractExistingFileAction.OverwriteSilently);
            }
            //else we do not need to because it is already extracted
        }

        public override void ProcessData()
        {
            dataArray = new List<string>();
            Excel2.Application app = new Excel2.Application();
            Excel2.Workbook book = app.Workbooks.Open(Directory.GetCurrentDirectory() + "\\" + mDate.Year + " hourly_loads.xls");
            Excel2.Worksheet sheet = findSheet(PECO, book);

            try
            {
                int y = 2;
                int x = 1;

                DateTime date = (DateTime)(sheet.Cells[y, x] as Excel2.Range).get_Value(Type.Missing);

                int error = 0;

                y = mDate.DayOfYear;
                //The day starts at 3, and goes for 24 hours to cell 27
                for (x = 3; x < 27; x++)
                {
                    DateTime d = date.AddHours(x - 3);
                    Excel2.Range range = (Excel2.Range)sheet.Cells[y, x];
                    if (range != null || range.Count != 0)
                    {
                        try
                        {
                            double value = (double)(sheet.Cells[y, x] as Excel2.Range).Value2;
                            string data = d.Hour + "," + value.ToString();
                            dataArray.Add(data);
                        }
                        catch (Exception)
                        { error++; }
                    }
                }
                date = date.AddDays(1);
                string timeStamp = mDate.Year + "_" + mDate.Month + "_" + mDate.Day;
                File.WriteAllLines(@"PJM-" + timeStamp + ".csv", dataArray.ToArray());
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
                closeExcelAppAndBook(app, book);
                return;
            }

            closeExcelAppAndBook(app, book);
        }

        //Find the PECO sheet
        private Excel2.Worksheet findSheet(string name, Excel2.Workbook book)
        {
            Excel2.Worksheet sheet = null;

            for (int i = 1; i <= book.Worksheets.Count; i++)
            {
                Excel2.Worksheet temp = (Excel2.Worksheet)book.Worksheets.get_Item(i);
                if (temp.Name.Equals(PECO))
                {
                    sheet = temp;
                }
            }

            return sheet;
        }

        private void closeExcelAppAndBook(Excel2.Application app, Excel2.Workbook book)
        {
            book.Close();
            app.Quit();
        }

        private List<string> dataArray;
        private DateTime mDate;
    }
}

